package com.zygh.questionnairesurvey.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zygh.questionnairesurvey.entity.Dept;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface DeptMapper extends BaseMapper<Dept> {
    /**
     * 去重查出问题下关联的所有大队
     *
     * @param lowest 问题id
     * @return 所有符合条件的大队
     */
    @Select("SELECT DISTINCT d.dept_id,d.name FROM questionnaire q\n" +
            "INNER JOIN dept d ON d.dept_id = q.dept_id \n" +
            "WHERE FIND_IN_SET(#{lowest},q.lowest) AND d.pid = 0\n" +
            "ORDER BY q.dept_id ASC")
    List<Dept> findByLowestAndDistinct(Integer lowest);

    /**
     * 查询工勤人员的分布的部门
     *
     * @return
     */
    @Select("<script> SELECT (SELECT t.sort FROM dept t WHERE t.dept_id = d.pid) sort," +
            "(SELECT t.dept_id FROM dept t WHERE t.dept_id = d.pid) dept_id," +
            "(SELECT t.name FROM dept t WHERE t.dept_id = d.pid) name FROM dept d LEFT JOIN user_info u ON d.dept_id = u.dept_id \n" +
            "WHERE u.term_status = 3 " +
            "<if test = 'id != null and id != 0 '> and d.pid = #{id} </if>" +
            " GROUP BY name ORDER BY sort ASC </script>")
    List<Dept> findDeptByWord(Integer id);

    /**
     * 根据父级部门id查询工勤人员分布的子级部门
     */
    @Select("<script> SELECT d.dept_id,d.name,d.sort FROM dept d LEFT JOIN user_info u ON d.dept_id = u.dept_id " +
            " WHERE u.term_status = 3 " +
            " and pid = #{id} " +
            " GROUP BY d.name ORDER BY sort ASC </script>")
    List<Dept> findDeptWordById(Integer id);

    /**
     * 通过父id查询子部门id
     *
     * @param deptId 部门id
     * @return 所有子部门id
     */
    @Select("SELECT d.dept_id FROM dept d \n" +
            "WHERE d.pid = #{deptId}")
    List<Integer> listDeptId(Integer deptId);

    /**
     * 去重查出问题下关联的部门id
     *
     * @param lowest 问题id
     * @return 所有符合条件的部门id
     */
    @Select("SELECT DISTINCT d.pid FROM questionnaire q\n" +
            "INNER JOIN dept d ON d.dept_id = q.dept_id \n" +
            "WHERE FIND_IN_SET(#{lowest},q.lowest)\n" +
            "ORDER BY q.dept_id ASC")
    List<Integer> findDeptId(Integer lowest);
}